import mysql.connector
import datetime


class MySQLConnector:
    def __init__(self, host, user, password, database):
        self.conn = mysql.connector.connect(
            host=host, user=user, password=password, database=database
        )
        self.cursor = self.conn.cursor()

    def execute(self, sql, values=None):
        if values:
            return self.cursor.execute(sql, values, multi=True)
        else:
            return self.cursor.execute(sql, multi=True)

    def fetchone(self):
        return self.cursor.fetchone()

    def fetchall(self):
        return self.cursor.fetchall()

    def query(self, sql, values=None):
        self.execute(sql, values)
        return self.fetchall()

    def insert(self, table, data):
        columns = ", ".join(data.keys())
        placeholders = ", ".join(["%s"] * len(data))
        sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        rs = self.execute(sql, tuple(data.values()))
        self.conn.commit()
        return rs

    def update(self, table, data, condition):
        set_values = ", ".join([f"{key} = '{value}'" for key, value in data.items()])
        sql = f"UPDATE {table} SET {set_values} WHERE {condition}"
        rs = self.execute(sql)
        self.conn.commit()
        return rs

    def delete(self, table, condition):
        sql = f"DELETE FROM {table} WHERE {condition}"
        rs = self.execute(sql)
        self.conn.commit()
        return rs

    def close(self):
        self.cursor.close()
        self.conn.close()


if __name__ == "__main__":

    # 初始化MySQL类
    db = MySQLConnector("127.0.0.1", "haojg", "haojg", "stocks")

    # 插入新记录
    data = {
        "id": 0,
        "name": "test-del",
        "age": 30,
        "create_time": datetime.datetime.now(),
    }
    db.insert("t_test_table", data)

    # 执行SELECT语句
    db.execute("SELECT * FROM t_test_table")

    # 获取一个结果
    print(db.fetchone())

    # 获取所有结果
    print(db.fetchall())

    # 插入新记录
    data = {
        "id": 0,
        "name": "test-1",
        "age": 30,
        "create_time": datetime.datetime.now(),
    }
    db.insert("t_test_table", data)

    # 更新记录
    data = {"name": "test-2"}
    condition = "id = 1"
    db.update("t_test_table", data, condition)

    # 删除记录
    condition = "name ='test-del'"
    db.delete("t_test_table", condition)

    # 关闭连接
    db.close()
